package meterMonitor.util.Timer;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import meterMonitor.mapper.MonitorDetailMapper;
import meterMonitor.mapper.MonitorMapper;
import meterMonitor.model.Meter;
import meterMonitor.model.MonitorDetail;
import meterMonitor.vo.ReportDaily;
import meterMonitor.vo.ReqDetails;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.scheduling.annotation.Scheduled;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 每月定时任务
 */
@Component
public class TimingController {
    @Autowired
    MonitorDetailMapper monitorDetailMapper;
    @Autowired
    MonitorMapper monitorMapper;

    /**
     * 原始报表定时每月一号导出上月数据成excel
     * @throws WriteException
     * @throws IOException
     */
    @Scheduled(cron = "0 0 4 1 * ?")//每月一号的4点触发
    public void TimingDetails() throws WriteException, IOException {
    Calendar c = Calendar.getInstance();
    c.add(Calendar.MONTH, -1);
     //1. 导出Excel的路径
    String filePath = "C:/ExportFile/TheOriginalReport/"+new SimpleDateFormat("yyyy年MM月").format(c.getTime())+"原始报表.xls";
    File file=new File("C:/ExportFile");
    File file1=new File("C:/ExportFile/TheOriginalReport");
    if(file.exists()){
        if(!file1.exists()){
            file1.mkdir();
        }
    }else{
        file.mkdir();
        if(!file1.exists()){
            file1.mkdir();
        }
    }
    WritableWorkbook wwb = null;
    try {
        wwb = Workbook.createWorkbook(new File(filePath));
    } catch (Exception e) {
        e.printStackTrace();
    }
    //创建Excel表的"原始报表"区域的数据
    WritableSheet sheet = wwb.createSheet("原始报表",0);
        ReqDetails re=new ReqDetails();
        re.setEndDate(null);
        re.setMeterCode(null);
        re.setStartDate(null);
    try {
    // List<MonitorDetail>  list=monitorDetailMapper.selectAll(re);
List<MonitorDetail>  list=new ArrayList<>();
    //要插入到的Excel表格的行号，默认从0开始
    Label pkid= new Label(0, 0, "序号");//表示第
    Label meterCode= new Label(1,0, "芯片编号");
    Label electricQty= new Label(2,0, "电量");
    Label currentQuantiry= new Label(3, 0, "当前读数");
    Label intervalQuantity= new Label(4, 0, "间隔使用量");
    Label intervalSec= new Label(5, 0, "间隔时间（秒）");
    Label uploadDate= new Label(6, 0, "所属日期");
    Label uploadTime= new Label(7, 0, "上传时间");
    Label ownerTimer= new Label(8, 0, "所属时间段");
    Label monitorId= new Label(9, 0, "对应monitor编号");
    Label addedTime= new Label(10, 0, "数据上传时间");
    Label lastModifyTime= new Label(11, 0, "最后修改时间");
    Label monitorHourId= new Label(12, 0, "对应monitor_hour编号");
    Label receiveQuantiry= new Label(13, 0, "原始读表数");
    Label receiveIntervalQuantity= new Label(14, 0, "原始间隔使用量");
    sheet.addCell(pkid);
    sheet.addCell(meterCode);
    sheet.addCell(electricQty);
    sheet.addCell(currentQuantiry);
    sheet.addCell(intervalQuantity);
    sheet.addCell(intervalSec);
    sheet.addCell(uploadDate);
    sheet.addCell(uploadTime);
    sheet.addCell(ownerTimer);
    sheet.addCell(monitorId);
    sheet.addCell(addedTime);
    sheet.addCell(lastModifyTime);
    sheet.addCell(monitorHourId);
    sheet.addCell(receiveQuantiry);
    sheet.addCell(receiveIntervalQuantity);
    for (int i=0;i<list.size();i++){
        sheet.addCell(new jxl.write.Number(0,i+1,list.get(i).getPkid()));
        sheet.addCell(new Label(1,i+1,list.get(i).getMeterCode().toString()));
        sheet.addCell(new jxl.write.Number(2,i+1,list.get(i).getElectricQty()));
        sheet.addCell(new jxl.write.Number(3,i+1,list.get(i).getCurrentQuantiry()));
        sheet.addCell(new jxl.write.Number(4,i+1,list.get(i).getIntervalQuantity()));
        // sheet.addCell(new jxl.write.Number(5,i+1,list.get(i).getIntervalSec()));
        // sheet.addCell(new Label(6,i+1,list.get(i).getUploadDate()));
        sheet.addCell(new jxl.write.DateTime(7,i+1,list.get(i).getUploadTime()));
        // sheet.addCell(new Label(8,i+1,list.get(i).getOwnerTimer()));
        // sheet.addCell(new jxl.write.Number(9,i+1,list.get(i).getMonitorId()));
        // sheet.addCell(new jxl.write.DateTime(10,i+1,list.get(i).getAddedTime()));
        // sheet.addCell(new jxl.write.DateTime(11,i+1,list.get(i).getLastModifyTime()));
        // sheet.addCell(new jxl.write.Number(12,i+1,list.get(i).getMonitorHourId()));
        // sheet.addCell(new jxl.write.Number(13,i+1,list.get(i).getReceiveQuantiry()));
        // sheet.addCell(new jxl.write.Number(14,i+1,list.get(i).getReceiveIntervalQuantity()));
    }
        wwb.write();
    }  catch (IOException e) {
        e.printStackTrace();
    } finally{
        wwb.close();
    }
    }

    /**
     * 导出上月日报表
     * @throws IOException
     * @throws WriteException
     */
    @Scheduled(cron = "0 10 4 1 * ?")//每月一号的4点10分触发
    public void TimingMonitor() throws IOException, WriteException {
        DecimalFormat df = new DecimalFormat("#0.00");
        List<Meter> Meter_Name=monitorMapper.Select_AllMeterName();
        String[] anotherNames=new String[Meter_Name.size()];
        for(int i=0;i<anotherNames.length;i++){
            anotherNames[i]= String.valueOf(Meter_Name.get(i).getAnotherName());
        }
        String fromDate=StartAndEndTime.StartTime();
        String toDate=StartAndEndTime.EndTime();

        //1. 导出Excel的路径
        String filePath = "C:/ExportFile/MonthlyDailyReport/"+fromDate+"-"+toDate+"报表.xls";
        File file=new File("C:/ExportFile");
        File file1=new File("C:/ExportFile/MonthlyDailyReport");
        if(file.exists()){
            if(!file1.exists()){
                file1.mkdir();
            }
        }else{
            file.mkdir();
            if(!file1.exists()){
                file1.mkdir();
            }
        }
        WritableWorkbook wwb = null;
        try {
            wwb = Workbook.createWorkbook(new File(filePath));
        } catch (IOException e) {
            e.printStackTrace();
        }
        //创建Excel表的"报表"区域的数据
        WritableSheet sheet = wwb.createSheet("报表",0);
        Label label1= new Label(0, 0, "日期");//表示第
        Label label2= new Label(1,0, "仪表位置");
        Label label3= new Label(2,0, "标准用量");
        Label label4= new Label(3, 0, "实际用量");
        Label label5= new Label(4, 0, "用水节约率");
        Label label6= new Label(5, 0, "是否超标");
        Label label7= new Label(6, 0, "电池耗量");
        Label label8= new Label(7, 0, "水表数值");
        sheet.addCell(label1);
        sheet.addCell(label2);
        sheet.addCell(label3);
        sheet.addCell(label4);
        sheet.addCell(label5);
        sheet.addCell(label6);
        sheet.addCell(label7);
        sheet.addCell(label8);
        List<ReportDaily> list=monitorMapper.getReportDaily(fromDate,toDate,null,anotherNames);
        for (int a=0;a<list.size();a++) {
            //日期
            String datecode = list.get(a).getDateCode();
            //仪表位置
            String devicename = list.get(a).getDeviceName();
            //标准用量
            String standardusage = list.get(a).getStandardUsage() + "";
            //实际用量
            Double ru = list.get(a).getRealUsage() ;
            ru=Double.valueOf(df.format(ru));
            String realusage=ru+"";
            //用水节约率
            String retes = null;
            if (list.get(a).getStandardUsage() > 0) {
                Double rate = ((list.get(a).getStandardUsage() - list.get(a).getRealUsage()) / list.get(a).getStandardUsage()) * 100;
                rate = Double.valueOf(df.format(rate));
                retes = rate + "%";
            }
            //是否超标
            boolean isOF = list.get(a).getRealUsage() > list.get(a).getStandardUsage();
            list.get(a).setOverFlow(isOF);
            list.get(a).setOverFlowName(isOF ? "是" : "否");
            String overflowname = list.get(a).getOverFlowName();
            //电池耗量
            Double electrqtr = list.get(a).getElectricQty();
            electrqtr = Double.valueOf(df.format(electrqtr));
            String electrqtrs = electrqtr + "%";
            //水表数值
            DecimalFormat decimalFormat = new DecimalFormat("###################.###########");
            String currentquantiry = decimalFormat.format(list.get(a).getCurrentQuantiry());
            sheet.addCell(new Label(0,a+1,datecode));
            sheet.addCell(new Label(1,a+1,devicename));
            sheet.addCell(new Label(2,a+1,standardusage));
            sheet.addCell(new Label(3,a+1,realusage));
            sheet.addCell(new Label(4,a+1,retes));
            sheet.addCell(new Label(5,a+1,overflowname));
            sheet.addCell(new Label(6,a+1,electrqtrs));
            sheet.addCell(new Label(7,a+1,currentquantiry));
        }
        wwb.write();
        wwb.close();
    }

    /**
     * 每月一日凌晨五点备份指定时间的数据并删除
     * @throws IOException
     * @throws ParseException
     */
        // @Scheduled(cron = "0 0 5 1 * ? ")//每月一日凌晨五点
        public void BackupsMonitorDetail() throws IOException, ParseException {
            DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            String presenttime=StartAndEndTime.getMonth01();
            Date presentdate=format.parse(presenttime);
            //日历对象
            Calendar calendar = Calendar.getInstance();
            //设置当前日期
             calendar.setTime(presentdate);
            //月份减一为-1，加一为1
            InputStream inStream = TimingController.class.getClassLoader().getResourceAsStream("runtime.properties");
            Properties prop = new Properties();
            prop.load(inStream);
            String key = prop.getProperty("reserve_time");
            int flag=Integer.parseInt(key);
            calendar.add(Calendar.MONTH, flag);
            Date res =calendar.getTime();
            String month01time=format.format(res);
            int monitor=monitorMapper.Backup_monitor(month01time);
            if(monitor>0){
                int aa=monitorMapper.delete_monitor(month01time);
            }
            int detail=monitorMapper.Backup_monitor_detail(month01time);
            if(detail>0){
                monitorMapper.delete_monitor_detail(month01time);
            }
            int hour=monitorMapper.Backup_monitor_hour(month01time);
            if(hour>0){
                monitorMapper.delete_monitor_hour(month01time);
            }
        }
}
